unit PaymentVoucherForm;

interface

uses
  Winapi.Windows, Winapi.Messages, System.SysUtils, System.Variants, System.Classes, Vcl.Graphics,
  Vcl.Controls, Vcl.Forms, Vcl.Dialogs, DBModule, Vcl.Grids, Vcl.DBGrids, DateUtils,
  Vcl.ComCtrls, Vcl.StdCtrls, UserUtils, Data.DB, frxClass, IBCustomDataSet,
  IBQuery, IB, IBTable, IBSQL, Vcl.DBCtrls, frxDBSet, CurrToWords, frxExportPDF;

type
  TfmPaymentVoucher = class(TForm)
    DBGrid1: TDBGrid;
    PageControl1: TPageControl;
    TabSheet1: TTabSheet;
    TabSheet2: TTabSheet;
    TabSheet3: TTabSheet;
    DBGrid2: TDBGrid;
    frxReport1: TfrxReport;
    DBGrid3: TDBGrid;
    dsJobsheet: TDataSource;
    qryJobsheet: TIBQuery;
    qrySalary: TIBQuery;
    btnPostToPV: TButton;
    qryPVMaster: TIBQuery;
    dsPVMaster: TDataSource;
    Label1: TLabel;
    DBGrid4: TDBGrid;
    Label2: TLabel;
    dsSalary: TDataSource;
    dsPVDetails: TDataSource;
    Label5: TLabel;
    txtYear: TEdit;
    udYear: TUpDown;
    Label6: TLabel;
    txtMonth: TEdit;
    udMonth: TUpDown;
    cbFilter: TCheckBox;
    qrySQL: TIBQuery;
    lblName: TLabel;
    dbcbName: TDBLookupComboBox;
    Label3: TLabel;
    txtDetails: TEdit;
    Label4: TLabel;
    txtSalaryDetails: TEdit;
    qryPVDetails: TIBQuery;
    frxDBConfig: TfrxDBDataset;
    frxDBPVReport: TfrxDBDataset;
    frxPDFExport1: TfrxPDFExport;
    qryPVReport: TIBQuery;
    btnPreview: TButton;
    Label7: TLabel;
    Label8: TLabel;
    txtOpenDetails: TEdit;
    txtOpenName: TEdit;
    Label9: TLabel;
    txtOpenRemark: TEdit;
    Label10: TLabel;
    txtOpenAmount: TEdit;
    Label11: TLabel;
    txtRemark: TEdit;
    procedure cbFilterClick(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure DBGrid2CellClick(Column: TColumn);
    procedure btnPostToPVClick(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure dbcbNameDropDown(Sender: TObject);
    procedure dbcbNameCloseUp(Sender: TObject);
    procedure DBGrid1CellClick(Column: TColumn);
    procedure btnPreviewClick(Sender: TObject);
    function frxReport1UserFunction(const MethodName: string;
      var Params: Variant): Variant;
    procedure DBGrid3CellClick(Column: TColumn);
    procedure txtOpenAmountExit(Sender: TObject);
    procedure txtOpenAmountKeyPress(Sender: TObject; var Key: Char);
  private
    CurrentPVNo: String;
    PVNo: Integer;

    procedure DoFilter;
    function GetCurrentPVNo: String;
    procedure UpdatePVNo;
    procedure PVDetailsFilter;
    { Private declarations }
  public
    { Public declarations }
  end;

var
  fmPaymentVoucher: TfmPaymentVoucher;

implementation

{$R *.dfm}

procedure TfmPaymentVoucher.btnPostToPVClick(Sender: TObject);
var
  i, PVID: integer;
  TotalAmount: Double;

begin
  CurrentPVNo := GetCurrentPVNo;
  with qrySQL do
  begin
    try  //insert payment voucher master record
      SQL.Clear;
      Params.Clear;
      SQL.Add('INSERT INTO Payment_Voucher (PV_NO, PAY_FOR, EMPLOYEE_ID, SUPPLIER_ID, ' +
              'DETAILS, REMARK, AMOUNT, AMOUNT_WORDS, PV_CODE, CREATE_DATE, MODIFIED_DATE) ' +
              'VALUES (:PVNo, :PayFor, :EmployeeID, :SupplierID, :Details, ' +
              ':Remark, :Amount, :AmountWords, :PVCode, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);');
      Params.ParamByName('PVNo').AsString := CurrentPVNo;
      case PageControl1.TabIndex of
        0: begin
          Params.ParamByName('PayFor').AsString := DBGrid2.DataSource.DataSet.FieldByName('Name').AsString;
          Params.ParamByName('EmployeeID').AsInteger := DBGrid2.DataSource.DataSet.FieldByName('ID').AsInteger;
          Params.ParamByName('Details').AsString := txtSalaryDetails.Text;
          Params.ParamByName('Remark').AsString := DBGrid2.DataSource.DataSet.FieldByName('Remark').AsString;
          Params.ParamByName('Amount').AsFloat := DBGrid2.DataSource.DataSet.FieldByName('Net_Pay').AsFloat;
          Params.ParamByName('AmountWords').AsString := ConvertCurrencyToEnglish(DBGrid2.DataSource.DataSet.FieldByName('Net_Pay').AsVariant);
          Params.ParamByName('PVCode').AsString := Format('SAL%4d%.2d%.4d', [DBGrid2.DataSource.DataSet.FieldByName('Year').AsInteger,
                                                                      DBGrid2.DataSource.DataSet.FieldByName('Month').AsInteger,
                                                                      DBGrid2.DataSource.DataSet.FieldByName('ID').AsInteger]);
        end;
        1: begin
          Params.ParamByName('PayFor').AsString := dbcbName.ListSource.DataSet.FieldByName('Name').AsString;
          Params.ParamByName('EmployeeID').AsInteger := dbcbName.ListSource.DataSet.FieldByName('ID').AsInteger;
          Params.ParamByName('Details').AsString := txtDetails.Text;
          Params.ParamByName('Remark').AsString := txtRemark.Text;
          TotalAmount := 0;
          for i := 0 to DBGrid3.SelectedRows.Count - 1 do
          begin
            DBGrid3.DataSource.DataSet.GotoBookmark(Pointer(DBGrid3.SelectedRows.Items[i]));
            TotalAmount := TotalAmount + DBGrid3.DataSource.DataSet.FieldByName('TOTAL_AMOUNT').AsFloat;
          end;
          Params.ParamByName('Amount').AsFloat := TotalAmount;
          Params.ParamByName('AmountWords').AsString := ConvertCurrencyToEnglish(TotalAmount);
          //PVCode = JSP + 4 digit employee ID, e.g. JSP0001
          Params.ParamByName('PVCode').AsString := Format('JSP%4d%.4d', [YearOf(Now), dbcbName.ListSource.DataSet.FieldByName('ID').AsInteger]);
        end;
        2: begin
          TotalAmount := StrToFloat(txtOpenAmount.Text);
          Params.ParamByName('PayFor').AsString := txtOpenName.Text;
          Params.ParamByName('Details').AsString := txtOpenDetails.Text;
          Params.ParamByName('Remark').AsString := txtOpenRemark.Text;
          Params.ParamByName('Amount').AsFloat := TotalAmount;
          Params.ParamByName('AmountWords').AsString := ConvertCurrencyToEnglish(TotalAmount);
          Params.ParamByName('PVCode').AsString := 'OPN';
        end;
      end;
      if not Prepared then
        Prepare;
      ExecSQL;

      try //get new inserted payment voucher ID
        SQL.Clear;
        Params.Clear;
        SQL.Add('SELECT ID FROM PAYMENT_VOUCHER WHERE PV_NO=:PVNo;');
        Params.ParamByName('PVNo').AsString := CurrentPVNo;
        if not Prepared then
          Prepare;
        Open;
        PVID := FieldByName('ID').AsInteger;
      except
        on E: EIBInterBaseError do
        begin
          Transaction.Rollback;
          ShowMessage(Format('Get payment voucher ID error. IBErrorCode=%d, SQLCode=%d, Msg=%s, StackTrace=%s', [E.IBErrorCode, E.SQLCode, E.Message, E.StackTrace]));
        end;
      end;

      case PageControl1.TabIndex of
        0: begin

        end;
        1: begin
          try  //insert payment voucher details ID of jobsheet
            for i := 0 to DBGrid3.SelectedRows.Count - 1 do
            begin
              DBGrid3.DataSource.DataSet.GotoBookmark(Pointer(DBGrid3.SelectedRows.Items[i]));
              SQL.Clear;
              Params.Clear;
              SQL.Add('INSERT INTO PV_DETAILS (PV_ID, PV_NO, DETAILS_ID) ' +
                      'VALUES (:PVID, :PVNo, :DetailsID);');
              Params.ParamByName('PVID').AsInteger := PVID;
              Params.ParamByName('PVNo').AsString := CurrentPVNo;
              Params.ParamByName('DetailsID').AsInteger := DBGrid3.DataSource.DataSet.FieldByName('ID').AsInteger;
              if not Prepared then
                Prepare;
              ExecSQL;
            end;
            Transaction.Commit;
          except
            on E: EIBInterBaseError do
            begin
              Transaction.Rollback;
              ShowMessage(Format('Add payment voucher details error. IBErrorCode=%d, SQLCode=%d, Msg=%s, StackTrace=%s', [E.IBErrorCode, E.SQLCode, E.Message, E.StackTrace]));
            end;
          end;
        end;
        2: begin

        end;
      end;

      UpdatePVNo;
      with DBGrid1.DataSource.DataSet do
      begin
        if not Active then
          Active := True;
        Last;
      end;
      with DBGrid2.DataSource.DataSet do
      begin
        if not Active then
          Active := True;
        First;
      end;
      with DBGrid3.DataSource.DataSet do
      begin
        if not Active then
          Active := True;
        First;
      end;
      with dbcbName.ListSource.DataSet do
      begin
        if not Active then
          Active := True;
        First;
      end;
      PVDetailsFilter;
    except
      on E: EIBInterBaseError do
      begin
        Transaction.Rollback;
        ShowMessage(Format('Add payment voucher master error. IBErrorCode=%d, SQLCode=%d, Msg=%s, StackTrace=%s', [E.IBErrorCode, E.SQLCode, E.Message, E.StackTrace]));
      end;
    end;
  end;
  txtOpenName.Text := '';
  txtOpenAmount.Text := '';
  txtOpenDetails.Text := '';
  txtOpenRemark.Text := '';
  txtDetails.Text := '';
  txtRemark.Text := '';
  txtSalaryDetails.Text := '';
end;

procedure TfmPaymentVoucher.btnPreviewClick(Sender: TObject);
var
  tmpSQL: string;
begin
  with qryPVReport do
  begin
    if Active then
      Close;
    tmpSQL := SQL.Text;
    SQL.Clear;
    Params.Clear;
    tmpSQL := SQLBeforeClause(tmpSQL, 'Where');
    tmpSQL := SQLBeforeClause(tmpSQL, 'Order By');
    SQL.Add(tmpSQL);
    SQL.Add('Where PAYMENT_VOUCHER.PV_NO=''' + DBGrid1.DataSource.DataSet.FieldByName('PV_NO').AsString + '''');
    Open;
  end;
  frxReport1.LoadFromFile('PaymentVoucher.fr3');
  frxReport1.ShowReport();
end;

procedure TfmPaymentVoucher.cbFilterClick(Sender: TObject);
begin
  DoFilter;
end;

procedure TfmPaymentVoucher.dbcbNameCloseUp(Sender: TObject);
var
  EmployeeID: Integer;
  tmpSQL: string;
begin
  EmployeeID := VarToInt(dbcbName.KeyValue);
  with qryJobsheet do
  begin
    tmpSQL := SQL.Text;
    SQL.Clear;
    Params.Clear;
    tmpSQL := SQLBeforeClause(tmpSQL, 'Where');
    tmpSQL := SQLBeforeClause(tmpSQL, 'Order By');
    SQL.Add(tmpSQL);
    if EmployeeID > 0 then
    begin
      SQL.Add('Where PV_DETAILS.ID Is Null');
      SQL.Add('And PHOTOGRAPHER1_ID=' + IntToStr(EmployeeID));
    end;
    SQL.Add('Order By INVOICE.JOBSHEET_NO');
    if Active then
      Close;
    Open;
  end;
  txtDetails.Text := '';
  txtRemark.Text := '';
end;

procedure TfmPaymentVoucher.dbcbNameDropDown(Sender: TObject);
begin
  with dbcbName.ListSource.DataSet do
  begin
    Last;
    First;
  end;
end;

procedure TfmPaymentVoucher.DBGrid1CellClick(Column: TColumn);
begin
  PVDetailsFilter;
end;

procedure TfmPaymentVoucher.DBGrid2CellClick(Column: TColumn);
var
  mDate: TDate;
  FormatSettings: TFormatSettings;
begin
  // when click employee salary list, try locate the PV, if true, disable the
  // Post to PV button
{  mDate := EncodeDate(StrToInt(txtYear.Text), StrToInt(txtMonth.Text), 1);
  btnPostToPV.Enabled := not DBGrid1.DataSource.DataSet.Locate('PV_NO',
    Format('PV/%s/%d/', [
      FormatDateTime('yyyymm', mDate),
      DBGrid2.DataSource.DataSet.FieldByName('ID').AsInteger]),
    [loPartialKey]);}
  FormatSettings := TFormatSettings.Create;
  try
  txtSalaryDetails.Text := 'Salary for ' +
    FormatSettings.ShortMonthNames[DBGrid2.DataSource.DataSet.FieldByName('Month').AsInteger] +
    ' ' + DBGrid2.DataSource.DataSet.FieldByName('Year').AsString;
  finally
    FreeAndNil(FormatSettings);
  end;
end;

procedure TfmPaymentVoucher.DBGrid3CellClick(Column: TColumn);
var
  i: Integer;
  Jobsheet: String;
begin
  Jobsheet := '';
  for i := 0 to DBGrid3.SelectedRows.Count - 1 do
  begin
    DBGrid3.DataSource.DataSet.GotoBookmark(Pointer(DBGrid3.SelectedRows.Items[i]));
    if Jobsheet <> '' then Jobsheet := Jobsheet + ', ';
    Jobsheet := Jobsheet + DBGrid3.DataSource.DataSet.FieldByName('JOBSHEET_NO').AsString;
  end;
  txtDetails.Text := 'Jobsheet: ' + Jobsheet;
end;

procedure TfmPaymentVoucher.DoFilter;
begin
  if Active then begin
    with qrySalary do
    begin
      SQL.Text := SQLBeforeClause(SQL.Text, 'ORDER BY');
      SQL.Text := SQLBeforeClause(SQL.Text, 'WHERE');
      if cbFilter.Checked then
        SQL.Text := SQL.Text +
                    ' WHERE PAY_SALARY."YEAR" = ' + txtYear.Text +
                    ' AND PAY_SALARY."MONTH" = ' + txtMonth.Text;
      SQL.Text := SQL.Text + ' ORDER BY EMPLOYEE.NAME';
    end;
    DBGrid2.DataSource.DataSet.Open;
  end;
end;

procedure TfmPaymentVoucher.FormClose(Sender: TObject;
  var Action: TCloseAction);
begin
  FreeAndNil(fmPaymentVoucher);
end;

procedure TfmPaymentVoucher.FormCreate(Sender: TObject);
var
  mYear, mMonth, mDay: Word;
begin
  DecodeDate(Now, mYear, mMonth, mDay);
  txtYear.Text := IntToStr(mYear);
  txtMonth.Text := IntToStr(mMonth);
  qrySalary.Active := True;
  qryJobsheet.Active := True;
  qryPVMaster.Active := True;
  PVDetailsFilter;
  frxDBConfig.DataSet.Active := True;

  frxReport1.AddFunction('function FileExist(s: string): Boolean;');
  frxReport1.AddFunction('function StrReplace(s:string; p1:string; p2:string): string;');
  frxReport1.AddFunction('function ShortMonthNames(i: integer): string;');
end;

function TfmPaymentVoucher.frxReport1UserFunction(const MethodName: string;
  var Params: Variant): Variant;
begin
  if MethodName = 'FILEEXIST' then
    Result := FileExists(Params[0])
  else if MethodName = 'STRREPLACE' then
    Result := StringReplace(Params[0], Params[1], Params[2], [rfReplaceAll])
  else if MethodName = 'SHORTMONTHNAMES' then
    Result := FormatSettings.ShortMonthNames[VarToInt(Params[0])];
end;

function TfmPaymentVoucher.GetCurrentPVNo: String;
var
  FormatStr: String;
begin
  with qrySQL do
  begin
    SQL.Clear;
    Params.Clear;
    try
      SQL.Add('Select PV_PREFIX, LAST_PV_NO, PV_NO_LENGTH from Config');
      if not Prepared then
        Prepare;
      Open;
      PVNo := FieldByName('LAST_PV_NO').AsInteger + 1;
      Result := Format('%s%d%.*d', [FieldByName('PV_PREFIX').AsString, YearOf(Now), FieldByName('PV_NO_LENGTH').AsInteger, PVNo]);
    except
      on E: EIBInterBaseError do
      begin
        ShowMessage(Format('IBErrorCode=%d, SQLCode=%d, Msg=%s, StackTrace=%s', [E.IBErrorCode, E.SQLCode, E.Message, E.StackTrace]));
        Result := '';
      end;
    end;
  end;
end;

procedure TfmPaymentVoucher.UpdatePVNo;
begin
  with qrySQL do
  begin
    SQL.Clear;
    Params.Clear;
    try
      SQL.Add('UPDATE Config SET LAST_PV_NO=:LastPVNo, Update_Date=CURRENT_TIMESTAMP');
      Params.ParamByName('LastPVNo').AsInteger := PVNo;
      if not Prepared then
        Prepare;
      ExecSQL;
      Transaction.Commit;
    except
      on E: EIBInterBaseError do
      begin
        Transaction.Rollback;
        ShowMessage(Format('IBErrorCode=%d, SQLCode=%d, Msg=%s, StackTrace=%s', [E.IBErrorCode, E.SQLCode, E.Message, E.StackTrace]));
      end;
    end;
  end;
end;

procedure TfmPaymentVoucher.PVDetailsFilter;
var
  tmpSQL: string;
begin
  with qryPVDetails do
  begin
    tmpSQL := SQL.Text;
    SQL.Clear;
    Params.Clear;
    tmpSQL := SQLBeforeClause(tmpSQL, 'Where');
    tmpSQL := SQLBeforeClause(tmpSQL, 'Order By');
    SQL.Add(tmpSQL);
    SQL.Add('Where PV_DETAILS.PV_NO=''' + DBGrid1.DataSource.DataSet.FieldByName('PV_NO').AsString + '''');
    SQL.Add('Order By INVOICE.JOBSHEET_NO');
    if Active then
      Close;
    Open;
  end;
end;

procedure TfmPaymentVoucher.txtOpenAmountExit(Sender: TObject);
begin
  txtOpenAmount.Text := Format('%.2f', [StrToFloat(txtOpenAmount.Text)]);
end;

procedure TfmPaymentVoucher.txtOpenAmountKeyPress(Sender: TObject;
  var Key: Char);
var
  i: integer;
begin
  case Key of
    '0'..'9':;
    '.': begin
           i := Pos('.', txtOpenAmount.Text);
           if i > 0 then
           begin
             Key := #0;
             txtOpenAmount.SelStart := i + 1;
             txtOpenAmount.SelLength := Length(txtOpenAmount.Text) - i;
           end;
         end;
    Char(VK_BACK):;
    else
      Key := #0;
  end;
end;

end.
